#include "DRDBSupport.h"

DRDBSupport::DRDBSupport(QObject *parent) :
    QObject(parent)
{
    dbName = "DayRecordV2.db";

    initDB();
}

bool DRDBSupport::initDB()
{
    QString sql;

    if (!QSqlDatabase::drivers().contains("QSQLITE"))
    {
        QMessageBox::critical(NULL, "Warning", "Unable to load database", "This demo needs the SQLITE driver.\n");
        return false;
    }

    QFile dfile("./" + dbName);
    if (dfile.exists())
    {
        QFile::setPermissions("./" + dbName, QFile::WriteOwner | QFile::ReadOwner);
    }

    //dbHandle = QSqlDatabase::addDatabase(new QSQLiteDriver(),"QSQLITE");
    dbHandle = QSqlDatabase::addDatabase("QSQLITE");
    dbHandle.setDatabaseName("./" + dbName);

    if (!dbHandle.open())
    {
        QMessageBox::critical(NULL, "Warning", "Open database fail. \n" + dbHandle.lastError().text());
        return false;
    }

    QStringList tables = dbHandle.tables();
    if (!tables.contains(DR_DB_TEXT_TBL))
    {
        sql.clear();
        sql.append("CREATE TABLE " + DR_DB_TEXT_TBL + " (");
        sql.append("\"id\" INTEGER(4,0) NOT NULL PRIMARY KEY,");
        sql.append("\"group\" INTEGER(4,0),");
        sql.append("\"alerm\" INTEGER(4,0),");
        sql.append("\"ctime\" TEXT(64,0),");
        sql.append("\"mtime\" TEXT(64,0),");
        sql.append("\"status\" TEXT(32,0),");
        sql.append("\"title\" TEXT(256,0),");
        sql.append("\"body\" TEXT(1024,0));");

        QSqlQuery query(dbHandle);
        if (!query.exec(sql))
        {
            dbHandle.close();
            QMessageBox::critical(NULL, "Warning", "Init the data tables fail. \n" + query.lastError().text() + "\n" + sql);
            return false;
        }
    }

    if (!tables.contains(DR_DB_ALERM_TBL))
    {
        sql.clear();
        sql.append("CREATE TABLE " + DR_DB_ALERM_TBL + " (");
        sql.append("\"id\" INTEGER(4,0) NOT NULL PRIMARY KEY,");
        sql.append("\"record\" INTEGER(4,0),");
        sql.append("\"ctime\" TEXT(64,0),");
        sql.append("\"mtime\" TEXT(64,0),");
        sql.append("\"status\" TEXT(32,0),");
        sql.append("\"title\" TEXT(256,0),");
        sql.append("\"body\" TEXT(1024,0));");

        QSqlQuery query(dbHandle);
        if (!query.exec(sql))
        {
            dbHandle.close();
            QMessageBox::critical(NULL, "Warning", "Init the data tables fail. \n" + query.lastError().text() + "\n" + sql);
            return false;
        }
    }

    if (!tables.contains(DR_DB_GROUP_TBL))
    {
        sql.clear();
        sql.append("CREATE TABLE \"" + DR_DB_GROUP_TBL + "\" (");
        sql.append("\"id\" INTEGER(4,0) NOT NULL PRIMARY KEY,");
        sql.append("\"name\" TEXT(256,0));");

        QSqlQuery query(dbHandle);
        if (!query.exec(sql))
        {
            dbHandle.close();
            QMessageBox::critical(NULL, "Warning", "Init the data tables fail. \n" + query.lastError().text() + "\n" + sql);
            return false;
        }

        sql.clear();
        sql.append("INSERT INTO " + DR_DB_ALERM_TBL + " VALUES(0, \" " + tr("Unnamed") + "\")");
        sql.append("INSERT INTO " + DR_DB_ALERM_TBL + " VALUES(1, \" " + tr("Work") + "\")");
        sql.append("INSERT INTO " + DR_DB_ALERM_TBL + " VALUES(2, \" " + tr("Life") + "\")");
    }

    return true;
}

void DRDBSupport::wolkGroupReq()
{
    QString sql;
    QList<QVariant> listParam;

    sql.clear();
    sql.append("SELECT `id`, `name` FROM group;");
    QSqlQuery query(dbHandle);
    if (!query.exec(sql))
    {
        dbHandle.close();
        QMessageBox::critical(NULL, "Warning", "Init the data tables fail. \n" + query.lastError().text() + "\n" + sql);
        return;
    }

    while (query.next())
    {
        listParam.clear();
        listParam.append(query.value(query.record().indexOf("id")));
        listParam.append(query.value(query.record().indexOf("name")));

        emit onWolkGroupRsp(listParam);
    }
}

void DRDBSupport::wolkRecordReq(int groupIndex)
{
    QString sql;
    QList<QVariant> listParam;

    sql.clear();
    sql.append("SELECT `id`, `group`, `alerm`, `ctime`, `mtime`, `status`, `title`, `body` FROM record WHERE `group`=" + QString::number(groupIndex) + ";");
    QSqlQuery query(dbHandle);
    if (!query.exec(sql))
    {
        dbHandle.close();
        QMessageBox::critical(NULL, "Warning", "Init the data tables fail. \n" + query.lastError().text() + "\n" + sql);
        return;
    }

    while (query.next())
    {
        listParam.clear();
        listParam.append(query.value(query.record().indexOf("id")));
        listParam.append(query.value(query.record().indexOf("group")));
        listParam.append(query.value(query.record().indexOf("alerm")));
        listParam.append(query.value(query.record().indexOf("ctime")));
        listParam.append(query.value(query.record().indexOf("mtime")));
        listParam.append(query.value(query.record().indexOf("status")));
        listParam.append(query.value(query.record().indexOf("title")));
        listParam.append(query.value(query.record().indexOf("body")));

        emit onWolkRecordRsp(listParam);
    }
}

void DRDBSupport::searchGroupReq(QString text)
{
    QString sql;
    QList<QVariant> listParam;

    if (text.isEmpty())
    {
        this->wolkGroupReq();
        return;
    }

    sql.clear();
    sql.append("SELECT `id`, `name` FROM group WHERE `name` like \"%" + text + "%\";");
    QSqlQuery query(dbHandle);
    if (!query.exec(sql))
    {
        dbHandle.close();
        QMessageBox::critical(NULL, "Warning", "Init the data tables fail. \n" + query.lastError().text() + "\n" + sql);
        return;
    }

    while (query.next())
    {
        listParam.clear();
        listParam.append(query.value(query.record().indexOf("id")));
        listParam.append(query.value(query.record().indexOf("name")));

        emit onSearchGroupRsp(listParam);
    }
}

void DRDBSupport::searchRecordReq(QString text, int groupIndex)
{
    QString sql;
    QList<QVariant> listParam;

    if (text.isEmpty())
    {
        this->wolkRecordReq(groupIndex);
        return;
    }

    sql.clear();
    sql.append("SELECT `id`, `group`, `alerm`, `ctime`, `mtime`, `status`, `title`, `body` FROM record WHERE `group`=" + QString::number(groupIndex) + " WHERE `body` like \"%"+ text +"%\";");
    QSqlQuery query(dbHandle);
    if (!query.exec(sql))
    {
        dbHandle.close();
        QMessageBox::critical(NULL, "Warning", "Init the data tables fail. \n" + query.lastError().text() + "\n" + sql);
        return;
    }

    while (query.next())
    {
        listParam.clear();
        listParam.append(query.value(query.record().indexOf("id")));
        listParam.append(query.value(query.record().indexOf("group")));
        listParam.append(query.value(query.record().indexOf("alerm")));
        listParam.append(query.value(query.record().indexOf("ctime")));
        listParam.append(query.value(query.record().indexOf("mtime")));
        listParam.append(query.value(query.record().indexOf("status")));
        listParam.append(query.value(query.record().indexOf("title")));
        listParam.append(query.value(query.record().indexOf("body")));

        emit onSearchRecordRsp(listParam);
    }
}

void DRDBSupport::newGroupReq(QString text)
{
    QString sql;
    QList<QVariant> listParam;

    sql.clear();
    sql.append("INSERT INTO group VALUE(NULL, \""+ text +"\");");
    QSqlQuery query(dbHandle);
    if (!query.exec(sql))
    {
        dbHandle.close();
        QMessageBox::critical(NULL, "Warning", "Init the data tables fail. \n" + query.lastError().text() + "\n" + sql);
        return;
    }

    sql.clear();
    sql.append("SELECT `id`, `name` FROM group WHERE `name` = \"" + text + "\";");
    QSqlQuery query1(dbHandle);
    if (!query1.exec(sql))
    {
        dbHandle.close();
        QMessageBox::critical(NULL, "Warning", "Init the data tables fail. \n" + query1.lastError().text() + "\n" + sql);
        return;
    }

    while (query1.next())
    {
        listParam.clear();
        listParam.append(query.value(query1.record().indexOf("id")));
        listParam.append(query.value(query1.record().indexOf("name")));

        emit onNewGroupRsp(listParam);
    }
}

void DRDBSupport::newAlermReq(QList<QVariant>)
{

}

void DRDBSupport::newTextReq(QList<QVariant>)
{

}

void DRDBSupport::deleteTextReq(int index)
{

}

void DRDBSupport::deleteGroupReq(int index)
{

}
